package uniLearn.control;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import uniLearn.data.*;

public class ResultControl extends DataControl {
	//define
	//SQL command
	private static String SQL_GET_MARKING_SCHEME = "SELECT `msOrder`, `name`, `criteria`, `mark` FROM `marking_scheme` WHERE `ac_id` = ? ORDER BY `msOrder` ASC;";
	private static String SQL_GET_DETAIL_RESULT = "SELECT `r`.`msOrder`, `name`, `criteria`, `feedback`, `r`.`mark`, `m`.`mark` as `full_mark` FROM `result` AS `r`, `marking_scheme` AS `m`"+
			"WHERE `r`.`ac_id` = `m`.`ac_id` AND `r`.`msOrder` = `m`.`msOrder` AND `r`.`ac_id` = ? AND `submitBy` = ? ORDER BY `r`.`msOrder` ASC;";
	private static String SQL_GET_BRIEF_RESULT = "SELECT `ag_id`, `fileName`, `submitTime`, `status`, SUM(`r`.`mark`) AS `mark` "+
			"FROM `submit_assignment` AS `s` LEFT JOIN `result` AS `r` " +
			"ON `r`.`ac_id` = `s`.`ac_id` AND `s`.`submitBy` = `r`.`submitBy` "+ 
			"WHERE `s`.`ac_id` = ? AND `s`.`submitBy` = ?;"; //<---parameter request here

	private static String SQL_GET_RESULT_LIST_BY_ASSIGNMENT = "SELECT `s`.`submitBy`, `ag_id`, `fileName`, `submitTime`, `status`, SUM(`r`.`mark`) AS `mark` "+
			"FROM `submit_assignment` AS `s` LEFT JOIN `result` AS `r` " +
			"ON `r`.`ac_id` = `s`.`ac_id` AND `s`.`submitBy` = `r`.`submitBy` "+ 
			"WHERE `s`.`ac_id` = ? "+ //<---parameter request here
			"GROUP BY `s`.`submitBy`;"; 
		
	private static String SQL_UPDATE_RESULT = "INSERT INTO `result` (`ac_id`, `submitBy`, `msOrder`, `mark`, `feedback`) VALUES (?, ?, ?, ?, ?) "+
			"ON DUPLICATE KEY UPDATE `mark` = VALUES(`mark`), `feedback` = VALUES(`feedback`);";
	
	/**Get the MarkingScheme of the assignment
	 * @param acId the Assignment ID
	 * @return a list of MarkingScheme of the assignment
	 * @throws Exception Exception return if any error
	 */
	public ArrayList<MarkingScheme> getMarkingScheme(int acId) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		
		ArrayList<MarkingScheme> listScheme = new ArrayList<MarkingScheme>();
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_MARKING_SCHEME);
		// set parameter
		preStmt.setInt(1, acId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		while(rs.next()) {
			int msOrder = rs.getInt("msOrder");
			String name = rs.getString("name");
			String criteria = rs.getString("criteria");
			BigDecimal mark = rs.getBigDecimal("mark");
			
			MarkingScheme ms = new MarkingScheme(acId, msOrder, name);
			ms.setCriteria(criteria);
			ms.setMark(mark);
			listScheme.add(ms);
		}
		rs.close();
		preStmt.close();
		
		return listScheme;
	}
	
	/**Get the detail of the Assignment result
	 * @param acId the Assignment Control ID
	 * @param submitBy The ID of who Submit the Assignment
	 * @return
	 * @throws Exception - any Error when run the command
	 */
	public ArrayList<DetailResult> getDetailResult(int acId, String submitBy) throws Exception {
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");
		
		ArrayList<DetailResult> listResult = new ArrayList<DetailResult>();
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_DETAIL_RESULT);
		// set parameter
		preStmt.setInt(1, acId);
		preStmt.setString(2, submitBy);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		while(rs.next()) {
			int msOrder = rs.getInt("msOrder");
			String name = rs.getString("name");
			String criteria = rs.getString("criteria");
			String feedback = rs.getString("feedback");
			BigDecimal mark = rs.getBigDecimal("mark");
			BigDecimal fullMark = rs.getBigDecimal("full_mark");
			
			DetailResult r = new DetailResult(acId, submitBy, msOrder, name);
			r.setFeedback(feedback);
			r.setCriteria(criteria);
			r.setMark(mark);
			r.setFullMark(fullMark);
			
			listResult.add(r);
		}
		rs.close();
		preStmt.close();
		return listResult;
	}
	
	/** Get the mark & the full mark of the assignment
	 * @param acId the ID of Assignment
	 * @param submitBy Who submit the assignment
	 * @return a 2 integer array, 0: mark of assignment 1: full mark of the assignment   
	 * @throws Exception
	 */
	public AssignmentResult getBriefResult(int acId, String submitBy) throws Exception {
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");
		
		AssignmentResult ar = null;
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_BRIEF_RESULT);
		// set parameter
		preStmt.setInt(1, acId);
		preStmt.setString(2, submitBy);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		if(rs.next()) {
			int agId = rs.getInt("ag_id");
			String fileName = rs.getString("fileName");
			Date submitTime = new Date(rs.getTimestamp("submitTime").getTime());
			SubmitStatus status = SubmitStatus.fromString(rs.getString("status"));
			BigDecimal mark = rs.getBigDecimal("mark");
			
			ar = new AssignmentResult(acId, submitBy, agId);
			ar.setFileName(fileName);
			ar.setSubmitTime(submitTime);
			ar.setStatus(status);
			ar.setMark(mark);
		}
		rs.close();
		preStmt.close();
		if(ar == null)
			throw new Exception("Can't found submit assignment record");
		
		return ar;
	}
	
	/** Get a list of sumbit assignment result from database by an id of assignment
	 * @param acId The id of the assignment collector 
	 * @return A list of brief result from database
	 * @throws Exception
	 */
	public ArrayList<AssignmentResult> getResultListByAssignment(int acId) throws Exception {
		if(acId < 1 )
			throw new Exception("Incorrect Assignment ID");
		
		ArrayList<AssignmentResult> listResult = new ArrayList<AssignmentResult>();
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_RESULT_LIST_BY_ASSIGNMENT);
		// set parameter
		preStmt.setInt(1, acId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		while(rs.next()) {
			String submitBy = rs.getString("submitBy");
			int agId = rs.getInt("ag_id");
			String fileName = rs.getString("fileName");
			Date submitTime = new Date(rs.getTimestamp("submitTime").getTime());
			SubmitStatus status = SubmitStatus.fromString(rs.getString("status"));
			BigDecimal mark = rs.getBigDecimal("mark");
			
			AssignmentResult ar = new AssignmentResult(acId, submitBy, agId);
			ar.setFileName(fileName);
			ar.setSubmitTime(submitTime);
			ar.setMark(mark);
			ar.setStatus(status);
			
			listResult.add(ar);
		}
		
		return listResult;
	}

	/** Update the marking result to the database
	 * @param acId the id of assignment collector
	 * @param submitBy the user of submit the assignment
	 * @param listResult the list of result of the submit assignment
	 * @throws Exception
	 */
	public void markAssignment(int acId, String submitBy, ArrayList<DetailResult> listResult) throws Exception {
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");
		
		//check Result format
		Iterator<DetailResult> lrI = listResult.iterator();
		while(lrI.hasNext()) {
			if(lrI.next().getMsOrder() < 1)
				throw new Exception("Incorrect Result Format");
		}
		
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_UPDATE_RESULT);
		preStmt.setInt(1, acId);
		preStmt.setString(2, submitBy);
		lrI = listResult.iterator();
		int affRow = 0;
		while(lrI.hasNext()) {
			DetailResult dr = lrI.next();
			preStmt.setInt(3, dr.getMsOrder());
			preStmt.setBigDecimal(4, dr.getMark());
			preStmt.setString(5, dr.getFeedback());
			
			affRow += preStmt.executeUpdate();
		}
		if(affRow < 1) {
			preStmt.close();
			throw new Exception("Can't update reuslt");
		}
		
		preStmt.close();
	}
	
}